{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "! pip install pymysql\n",
    "! pip install mysql-connector-python\n",
    "! pip install sqlalchemy\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('information_schema',)\n",
      "('mysql',)\n",
      "('performance_schema',)\n",
      "('ruoyi',)\n",
      "('scala',)\n",
      "('spring',)\n",
      "('sys',)\n",
      "('test',)\n"
     ]
    }
   ],
   "source": [
    "import mysql.connector\n",
    "\n",
    "mydb = mysql.connector.connect(\n",
    "  host=\"localhost\",\n",
    "  user=\"root\",\n",
    "  password=\"021009\"\n",
    ")\n",
    "\n",
    "mycursor = mydb.cursor(buffered=True)\n",
    "\n",
    "mycursor.execute(\"SHOW DATABASES\")\n",
    "\n",
    "\n",
    "for x in mycursor:\n",
    "  print(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "mycursor.execute(\"\"\"\n",
    "create database test\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('customers',)\n"
     ]
    }
   ],
   "source": [
    "mycursor.execute(\"use test\")\n",
    "\n",
    "mycursor.execute(\"CREATE TABLE if not exists customers (name VARCHAR(255), address VARCHAR(255))\")\n",
    "\n",
    "mycursor.execute(\"SHOW TABLES\")\n",
    "\n",
    "for i in mycursor:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 was inserted.\n"
     ]
    }
   ],
   "source": [
    "sql = \"INSERT INTO customers (name, address) VALUES (%s, %s)\"\n",
    "val = [\n",
    "  ('Peter', 'Lowstreet 4'),\n",
    "  ('Amy', 'Apple st 652'),\n",
    "  ('Hannah', 'Mountain 21'),\n",
    "  ('Michael', 'Valley 345'),\n",
    "  ('Sandy', 'Ocean blvd 2'),\n",
    "  ('Betty', 'Green Grass 1'),\n",
    "  ('Richard', 'Sky st 331'),\n",
    "  ('Susan', 'One way 98'),\n",
    "  ('Vicky', 'Yellow Garden 2'),\n",
    "  ('Ben', 'Park Lane 38'),\n",
    "  ('William', 'Central st 954'),\n",
    "  ('Chuck', 'Main Road 989'),\n",
    "  ('Viola', 'Sideway 1633')\n",
    "]\n",
    "\n",
    "mycursor.executemany(sql, val)\n",
    "\n",
    "mydb.commit()\n",
    "\n",
    "print(mycursor.rowcount, \"was inserted.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'list'>\n",
      "<class 'list'>\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[name: Peter\taddress: Lowstreet 4,\n",
       " name: Amy\taddress: Apple st 652,\n",
       " name: Hannah\taddress: Mountain 21,\n",
       " name: Michael\taddress: Valley 345,\n",
       " name: Sandy\taddress: Ocean blvd 2,\n",
       " name: Betty\taddress: Green Grass 1,\n",
       " name: Richard\taddress: Sky st 331,\n",
       " name: Susan\taddress: One way 98,\n",
       " name: Vicky\taddress: Yellow Garden 2,\n",
       " name: Ben\taddress: Park Lane 38,\n",
       " name: William\taddress: Central st 954,\n",
       " name: Chuck\taddress: Main Road 989,\n",
       " name: Viola\taddress: Sideway 1633]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mycursor.execute(\"SELECT * FROM customers\")\n",
    "\n",
    "myresult = mycursor.fetchall()\n",
    "\n",
    "\n",
    "class Customer:\n",
    "    def __init__(self, name, address) -> None:\n",
    "        self.name = name\n",
    "        self.address = address\n",
    "\n",
    "    def __str__(self) -> str:\n",
    "        return f\"name: {self.name}\\taddress: {self.address}\"\n",
    "    \n",
    "    def __repr__(self) -> str:\n",
    "        return self.__str__()\n",
    "\n",
    "\n",
    "print(type(myresult))\n",
    "\n",
    "customers = list(map(lambda x: Customer(x[0], x[1]), myresult))\n",
    "print(type(customers))\n",
    "customers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('Peter', 'Lowstreet 4')\n"
     ]
    }
   ],
   "source": [
    "mycursor.execute(\"SELECT * FROM customers\")\n",
    "\n",
    "myresult = mycursor.fetchone()\n",
    "\n",
    "print(myresult)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "qwenv",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.3"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
